--EXEC SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE 4596, 25975, 'K97433.1', 'K97433.1', 55022, '3;4;24', null, 'AdaptorLot'
ALTER PROCEDURE SP_CREATE_UPDATE_BIN_INFO_FOR_ASSEMBLY_RELEASE
(
	@DB_PROCESSSTEP_ID	INT,
	@DB_LOT_ID			INT,
	@LOT_NUMBER			VARCHAR(50),
	@FAB_LOT_NUMBER		VARCHAR (50),
	@QTY_IN				INT,
	@DIEBANK_WAFER_IDS  VARCHAR(200),
	@P_TYPE				VARCHAR(20),
	@LOT_FROM			VARCHAR(50)	
)

AS 
BEGIN
	DECLARE @SORT_PROCESSSTEP_ID	INT
	DECLARE @FAB_PROCESSSTEP_ID		INT
	DECLARE @FAB_LOT_ID				INT
	DECLARE @SORT_LOT_ID			INT
	DECLARE @WAFER_IDS_TABLE		TABLE ( WAFER_ID INT )
	DECLARE @RESULT_WAFER_IDS_TABLE	TABLE ( WAFER_ID INT )
	DECLARE @SHIPPED_WAFER_IDS		VARCHAR(200)
	DECLARE @SHIPPED_WAFER_ID		VARCHAR(200)
	DECLARE @ALL_SHIPPED_WAFER_IDS  VARCHAR(200)
	DECLARE @DEFAULT_WAFERS         VARCHAR(200)
	DECLARE @DEFAULT_WAFER_IDS_TABLE		TABLE ( WAFER_ID INT ) 
	DECLARE @WAFER_COUNT			INT
	DECLARE @WAFER_ID     			INT
	DECLARE @BIN_QTY				INT
	DECLARE @MODULUS_REMAIN_QTY		INT
	DECLARE @TEMP_COUNT				INT
	DECLARE @GROSS_DIE_QTY			INT
	DECLARE @NO_OF_WAFERS			INT
	DECLARE @DIE_QTY				INT
	DECLARE @FAB_WAFER_IDS			VARCHAR(200)
	DECLARE @SORT_WAFER_IDS			VARCHAR(200)
	DECLARE @BYNEXTLOCATION INT
	DECLARE @DIEBANK_VENDOR			VARCHAR(50)
	DECLARE @SO_NO INT
	DECLARE @wafer_cursor CURSOR 
	DECLARE @LOT_STRING VARCHAR(50)

	SET @FAB_WAFER_IDS = NULL
	SET @SORT_WAFER_IDS = NULL
		
	--SET @DEFAULT_WAFERS = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25'

	SELECT @DIEBANK_VENDOR = L_Vendor_Name FROM Location WHERE Location_ID = ( SELECT Location_ID FROM Lot_Table where Lot_ID = @DB_LOT_ID )

	--INSERT INTO @DEFAULT_WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @DEFAULT_WAFERS )
			
	SELECT @GROSS_DIE_QTY = ISNULL(PARAMETER_VALUE,0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID(@DB_PROCESSSTEP_ID,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_KEY = 'Gross Die/Wafer'
	
	IF @DIEBANK_WAFER_IDS IS NOT NULL AND @LOT_FROM = 'adaptorLot'
	BEGIN
			SET @DIEBANK_WAFER_IDS = REPLACE( @DIEBANK_WAFER_IDS, ';', ',')
			INSERT INTO @WAFER_IDS_TABLE (WAFER_ID) SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @DIEBANK_WAFER_IDS )
	END
	SELECT TOP 1 @SORT_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @DB_PROCESSSTEP_ID,0 ) WHERE PS_TYPE = 'SORT'
	PRINT @SORT_PROCESSSTEP_ID
	--HERE FOR THE BLIND BUILD WAFERS WE DON"T HAVE SORT PROCESS, So GDPW is GOOD DIE 
	IF @SORT_PROCESSSTEP_ID IS NOT NULL
	BEGIN
		SET @wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
		OPEN @wafer_cursor
		FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
		WHILE @@FETCh_STATUS = 0
		BEGIN
			IF CHARINDEX( '.', @LOT_NUMBER, 0 ) > 0
			BEGIN
				SET @LOT_NUMBER = SUBSTRING( @LOT_NUMBER, 0, CHARINDEX( '.', @LOT_NUMBER ) )	
			END
			
			IF  @WAFER_ID  < 10
			BEGIN
				
				SET @LOT_STRING = @LOT_NUMBER + '-0' + CONVERT(VARCHAR(5), @WAFER_ID )	
			END
			ELSE
			BEGIN
				SET @LOT_STRING = @LOT_NUMBER + '-' + CONVERT(VARCHAR(5), @WAFER_ID )	
			END				
			--PRINT @LOT_STRING
			SELECT TOP 1 @SORT_LOT_ID = LOT_ID FROM LOT_TABLE WHERE LOT_NUMBER LIKE @LOT_STRING+ '%' AND Location_ID in ( SELECT Location_ID from Location WHERE L_Type = 'SORT' )
			IF @SORT_LOT_ID IS NOT NULL
			BEGIN
				IF ( (SELECT COUNT(WAFER_ID) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOT_ID AND WAFER_ID = @WAFER_ID) = 0 )
				BEGIN
					--HERE WE NEED TO DECIDE. IS BIN QTY IS GDPW
					EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@GROSS_DIE_QTY,0/*rejectedqty*/, 0.0/*yield*/	
				END
				ELSE
				BEGIN
					SELECT @BIN_QTY = BIN_1_Balance_Qty FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @SORT_LOT_ID AND WAFER_ID = @WAFER_ID
					EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@BIN_QTY,0/*rejectedqty*/, 0.0/*yield*/
				END
			END
			SET @LOT_STRING = NULL
			SET @WAFER_ID = NULL
			SET @BIN_QTY  = NULL
			FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
		END
		DELETE FROM @WAFER_IDS_TABLE
		CLOSE @wafer_cursor 
		DEALLOCATE @wafer_cursor
	END--END OF SORT PROCESS STEP ID
	ELSE -- WHEN SORT PROCESS NOT FOUND
	BEGIN
		--WE CAN REMOVE THE WAFER THAT EXISTS IN THE INFORMATIVE_BINNING FOR DIE BANK LOT FROM @WAFER_IDS_TABLE
		SELECT TOP 1 @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @DB_PROCESSSTEP_ID,0 ) WHERE PS_TYPE = 'FAB'
		IF @FAB_PROCESSSTEP_ID IS NOT NULL
		BEGIN
			SET @wafer_cursor = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @wafer_cursor
			FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
			WHILE @@FETCh_STATUS = 0
			BEGIN
					EXEC SP_SAVE_INFORMATIVE_BINNING_INFO @DB_LOT_ID, @WAFER_ID,'Bin1',@GROSS_DIE_QTY,0/*rejectedqty*/, 0.0/*yield*/
					
				SET @WAFER_ID = NULL
				FETCH NEXT FROM @wafer_cursor INTO @WAFER_ID
			END
			DELETE FROM @WAFER_IDS_TABLE
			CLOSE @wafer_cursor 
			DEALLOCATE @wafer_cursor
		END
	END
	IF ( (SELECT COUNT(WAFER_ID) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @DB_LOT_ID) = 25 )
	BEGIN
		UPDATE Lot_Table SET SHOP_ORDER_NUMBER = 1 WHERE Lot_ID = @DB_LOT_ID
	END
	ELSE
	BEGIN
		UPDATE Lot_Table SET SHOP_ORDER_NUMBER = 0 WHERE Lot_ID = @DB_LOT_ID
	END				
END
